﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Threading;

namespace l1cs.server.datatables
{
    class _00MysqlSample
    {
        // Java の super() は : base() {
        // Java の this() は : this() {
        //rs.getInt\("(.+)"\) -> Convert.ToInt32(item["\1"])
        //rs.getString\(".+"\) -> Convert.ToString(item["\1"])

        public void DateTimeSample()
        {
            // 現在のDateTime
            DateTime nowdatetime = DateTime.Now;
            // 今日の日
            int nowDay = DateTime.Today.Day;
            int nowYear = DateTime.Today.Year;
            int nowMonth = DateTime.Today.Month;
            nowDay = nowdatetime.Day;
            nowYear = nowdatetime.Year;
            nowMonth = nowdatetime.Month;
            int nowHour = nowdatetime.Hour;
            int nowMin = nowdatetime.Minute;
            int nowSec = nowdatetime.Second;
            int nowMsec = nowdatetime.Millisecond;

        }
        public void ThreadFunction( Object state )
        {
        }

        public void ThreadFunction2()
        {
        }
        class ThreadClass
        {
            int _delay;
            int _period;
            public ThreadClass( int delay, int period )
            {
                _delay = delay;
                _period = period;
            }
            public void run( Object state )
            {
                Thread.Sleep( _delay );
                while ( true )
                {
                    Thread.Sleep( _period );
                }
            }

        }
        public void sample()
        {
            Thread thread = null;
            thread = new Thread( new ThreadStart( ThreadFunction2 ) );
            thread.Start();

            // スレッドプール
            ThreadPool.QueueUserWorkItem( new WaitCallback( ThreadFunction ) );

            // scheduleAtFixedRate
            ThreadPool.QueueUserWorkItem( new WaitCallback( ThreadFunction ) );
            // ThreadFunction側で Thread.Sleep( _disconnectTimeMillis ); する
            // while ( true ) {とする

            // 文字列からインスタンス作成 使用箇所 TrapTableのcreateTrapInstance
            String className = "className";
            String storage = "storage";
            Type t = Type.GetType( className );
            object obj = t.InvokeMember( null,
                BindingFlags.CreateInstance,
                null, null,
                new object[] { storage } );

        }

        public void sqlUpdate()
        {
            //String sqlstr = "UPDATE characters SET OnlineStatus=0";
            //MySqlConnection con = L1DatabaseFactory.getInstance().getConnection();
            //MySqlCommand cmd = new MySqlCommand( sqlstr, con );
            //cmd.Parameters.Add( new MySqlParameter( "?val", 10 ) );
            //cmd.ExecuteNonQuery();
            //con.Close();
        }

        public void sqlSelect()
        {
            //String sqlstr = "SELECT * FROM characters";
            //MySqlConnection con = L1DatabaseFactory.getInstance().getConnection();
            //MySqlDataAdapter da = new MySqlDataAdapter( sqlstr, con );
            //DataTable dt = new DataTable();
            //da.Fill( dt );
            //con.Close();
            //foreach ( DataRow item in dt.Rows )
            //{
            //    String name = item["char_name"] as string;
            //    int objid = Convert.ToInt32( item["objid"] );
            //}
        }

        public void sqlSelect2()
        {
            //    String sqlstr = "SELECT * FROM characters";
            //    MySqlConnection con = L1DatabaseFactory.getInstance().getConnection();
            //    MySqlCommand cmd = new MySqlCommand( sqlstr, con );
            //    MySqlDataReader myReader;
            //    myReader = cmd.ExecuteReader();
            //    while ( myReader.Read() )
            //    {
            //        _log.info( myReader.GetString( 0 ) );
            //    }
        }

        private void sqlPrepare()
        {
            //String sqlstr = "INSERT INTO mytable VALUES (?val)";
            //MySqlConnection con = L1DatabaseFactory.getInstance().getConnection();
            //MySqlCommand cmd = new MySqlCommand( sqlstr, con );
            //cmd.Parameters.Add( new MySqlParameter( "?val", 10 ) );
            //cmd.ExecuteNonQuery();
            //con.Close();
        }

        // http://dev.mysql.com/doc/refman/5.1/ja/connector-net-examples-mysqlcommand.html より
        /*
         * 以下は、MySqlCommand と MySqlConnection を作成する例です。
         * MySqlConnection が開かれ、MySqlCommand の Connection として設定されます。
         * この例はその後 ExecuteNonQuery を呼び出し、接続を閉じます。
         * これを完遂するため、ExecuteNonQuery は SQL INSERT 文である接続ストリングとクエリ ストリングに渡されます。 
         */
        public void InsertRow( String myConnectionString )
        {
            // If the connection String is null, use a default.
            if ( myConnectionString == "" )
            {
                myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass";
            }
            MySqlConnection myConnection = new MySqlConnection( myConnectionString );
            String myInsertQuery = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)";
            MySqlCommand myCommand = new MySqlCommand( myInsertQuery );
            myCommand.Connection = myConnection;
            myConnection.Open();
            /*
             * ExecuteNonQuery
             * 接続に対して SQL 文を実行し、影響される行の数を返すします。 
             */
            myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();
        }

        /*
         * 以下の例は MySqlCommand を作成し、そのプロパティの一部を設定します。
         * この例は、MySqlCommand のオーバーロードされたコンストラクタのバージョンの使用法のひとつを挙げています。
         * 利用可能な他の例に関しては、各オーバーロードのトピックをご覧ください。 
         */
        public void CreateMySqlCommand()
        {
            MySqlConnection myConnection = new MySqlConnection( "Persist Security Info=False;database=test;server=myServer" );
            myConnection.Open();
            MySqlTransaction myTrans = myConnection.BeginTransaction();
            String mySelectQuery = "SELECT * FROM myTable";
            MySqlCommand myCommand = new MySqlCommand( mySelectQuery, myConnection, myTrans );
            myCommand.CommandTimeout = 20;
            // MySqlCommandのコンストラクターは以下の通り
            /* public MySqlCommand();
             * public MySqlCommand(String cmdText);
             * public MySqlCommand(String cmdText, MySqlConnection connection);
             * public MySqlCommand(String cmdText, MySqlConnection connection, MySqlTransaction transaction);
             */
        }
        /*
         * MySqlDataReader は、大きなバイナリ値の効率的な読み取りを可能にする特殊なモードをサポートしています。
         * 詳細は、CommandBehavior の SequentialAccess 設定を参照してください。 
         */
        public void CreateMySqlDataReader( String mySelectQuery, MySqlConnection myConnection )
        {
            MySqlCommand myCommand = new MySqlCommand( mySelectQuery, myConnection );
            myConnection.Open();
            MySqlDataReader myReader;
            myReader = myCommand.ExecuteReader();
            try
            {
                while ( myReader.Read() )
                {
                    Console.WriteLine( myReader.GetString( 0 ) );
                }
            }
            finally
            {
                myReader.Close();
                myConnection.Close();
            }
        }
        private void PrepareExample()
        {
            MySqlConnection myConnection = new MySqlConnection( "Persist Security Info=False;database=test;server=myServer" );
            MySqlCommand cmd = new MySqlCommand( "INSERT INTO mytable VALUES (?val)", myConnection );
            cmd.Parameters.Add( new MySqlParameter( "?val", 10 ) );
            cmd.Prepare();
            cmd.ExecuteNonQuery();

            cmd.Parameters[0].Value = 20;
            cmd.ExecuteNonQuery();
        }

        public void CreateMySqlCommand( MySqlConnection myConnection, String mySelectQuery, MySqlParameter[] myParamArray )
        {
            MySqlCommand myCommand = new MySqlCommand( mySelectQuery, myConnection );
            myCommand.CommandText = "SELECT id, name FROM mytable WHERE age=?age";
            myCommand.Parameters.Add( myParamArray );
            for ( int j = 0; j < myParamArray.Length; j++ )
            {
                myCommand.Parameters.Add( myParamArray[j] );
            }
            String myMessage = "";
            for ( int i = 0; i < myCommand.Parameters.Count; i++ )
            {
                myMessage += myCommand.Parameters[i].ToString() + "\n";
            }
            //MessageBox.Show(myMessage);
        }

        public static DataSet SelectRows( String myConnection, String mySelectQuery, String myTableName )
        {
            MySqlConnection myConn = new MySqlConnection( myConnection );
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
            myDataAdapter.SelectCommand = new MySqlCommand( mySelectQuery, myConn );
            MySqlCommandBuilder cb = new MySqlCommandBuilder( myDataAdapter );
            myConn.Open();
            DataSet ds = new DataSet();
            myDataAdapter.Fill( ds, myTableName );
            //code to modify data in DataSet here
            //Without the MySqlCommandBuilder this line would fail
            myDataAdapter.Update( ds, myTableName );
            myConn.Close();
            return ds;
        }
        public void InsertRow2( String myConnectionString )
        {
            // If the connection String is null, use a default.
            if ( myConnectionString == "" )
            {
                myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass";
            }
            MySqlConnection myConnection = new MySqlConnection( myConnectionString );
            String myInsertQuery = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)";
            MySqlCommand myCommand = new MySqlCommand( myInsertQuery );
            myCommand.Connection = myConnection;
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();
        }
        public void RunTransaction( String myConnString )
        {
            MySqlConnection myConnection = new MySqlConnection( myConnString );
            myConnection.Open();
            MySqlCommand myCommand = myConnection.CreateCommand();
            MySqlTransaction myTrans;
            // Start a local transaction
            myTrans = myConnection.BeginTransaction();
            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                myCommand.CommandText = "insert into Test (id, desc) VALUES (100, 'Description')";
                myCommand.ExecuteNonQuery();
                myCommand.CommandText = "insert into Test (id, desc) VALUES (101, 'Description')";
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                Console.WriteLine( "Both records are written to database." );
            }
            catch ( Exception e )
            {
                try
                {
                    myTrans.Rollback();
                }
                catch ( SqlException ex )
                {
                    if ( myTrans.Connection != null )
                    {
                        Console.WriteLine( "An exception of type " + ex.GetType() +
                                          " was encountered while attempting to roll back the transaction." );
                    }
                }

                Console.WriteLine( "An exception of type " + e.GetType() +
                                  " was encountered while inserting the data." );
                Console.WriteLine( "Neither record was written to database." );
            }
            finally
            {
                myConnection.Close();
            }
        }
        public void RunTransaction2( String myConnString )
        {
            MySqlConnection myConnection = new MySqlConnection( myConnString );
            myConnection.Open();
            MySqlCommand myCommand = myConnection.CreateCommand();
            MySqlTransaction myTrans;
            // Start a local transaction
            myTrans = myConnection.BeginTransaction();
            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                myCommand.CommandText = "insert into Test (id, desc) VALUES (100, 'Description')";
                myCommand.ExecuteNonQuery();
                myCommand.CommandText = "insert into Test (id, desc) VALUES (101, 'Description')";
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                Console.WriteLine( "Both records are written to database." );
            }
            catch ( Exception e )
            {
                try
                {
                    myTrans.Rollback();
                }
                catch ( SqlException ex )
                {
                    if ( myTrans.Connection != null )
                    {
                        Console.WriteLine( "An exception of type " + ex.GetType() +
                                          " was encountered while attempting to roll back the transaction." );
                    }
                }

                Console.WriteLine( "An exception of type " + e.GetType() +
                                  " was encountered while inserting the data." );
                Console.WriteLine( "Neither record was written to database." );
            }
            finally
            {
                myConnection.Close();
            }
        }
        public void CreateMySqlConnection()
        {
            String myConnString = "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass";
            MySqlConnection myConnection = new MySqlConnection( myConnString );
            myConnection.Open();
            //MessageBox.Show("Server Version: " + myConnection.ServerVersion + "\nDatabase: " + myConnection.Database);
            myConnection.ChangeDatabase( "test2" );
            //MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + "\nDatabase: " + myConnection.Database);
            myConnection.Close();
        }
        public DataSet SelectRows( DataSet dataset, String connection, String query )
        {
            MySqlConnection conn = new MySqlConnection( connection );
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = new MySqlCommand( query, conn );
            adapter.Fill( dataset );
            return dataset;
        }
        public static void CreateSqlDataAdapter()
        {
            MySqlConnection conn = new MySqlConnection( "Data Source=localhost;database=test" );
            MySqlDataAdapter da = new MySqlDataAdapter();
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            da.SelectCommand = new MySqlCommand( "SELECT id, name FROM mytable", conn );
            da.InsertCommand = new MySqlCommand( "INSERT INTO mytable (id, name) " + "VALUES (?id, ?name)", conn );
            da.UpdateCommand = new MySqlCommand( "UPDATE mytable SET id=?id, name=?name " + "WHERE id=?oldId", conn );
            da.DeleteCommand = new MySqlCommand( "DELETE FROM mytable WHERE id=?id", conn );
            da.InsertCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.InsertCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );

            da.UpdateCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.UpdateCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );
            da.UpdateCommand.Parameters.Add( "?oldId", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
            da.DeleteCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
        }
        public static void CreateSqlDataAdapter2()
        {
            MySqlConnection conn = new MySqlConnection( "Data Source=localhost;database=test" );
            MySqlCommand cmd = new MySqlCommand( "SELECT id, name FROM mytable", conn );
            MySqlDataAdapter da = new MySqlDataAdapter( cmd );
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            da.InsertCommand = new MySqlCommand( "INSERT INTO mytable (id, name) " + "VALUES (?id, ?name)", conn );
            da.UpdateCommand = new MySqlCommand( "UPDATE mytable SET id=?id, name=?name " + "WHERE id=?oldId", conn );
            da.DeleteCommand = new MySqlCommand( "DELETE FROM mytable WHERE id=?id", conn );
            da.InsertCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.InsertCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );

            da.UpdateCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.UpdateCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );
            da.UpdateCommand.Parameters.Add( "?oldId", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
            da.DeleteCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
        }
        public static void CreateSqlDataAdapter3()
        {
            MySqlConnection conn = new MySqlConnection( "Data Source=localhost;database=test" );
            MySqlDataAdapter da = new MySqlDataAdapter( "SELECT id, name FROM mytable", conn );
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            da.InsertCommand = new MySqlCommand( "INSERT INTO mytable (id, name) " + "VALUES (?id, ?name)", conn );
            da.UpdateCommand = new MySqlCommand( "UPDATE mytable SET id=?id, name=?name " + "WHERE id=?oldId", conn );
            da.DeleteCommand = new MySqlCommand( "DELETE FROM mytable WHERE id=?id", conn );
            da.InsertCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.InsertCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );

            da.UpdateCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.UpdateCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );
            da.UpdateCommand.Parameters.Add( "?oldId", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
            da.DeleteCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
        }
        public static void CreateSqlDataAdapter4()
        {
            MySqlDataAdapter da = new MySqlDataAdapter( "SELECT id, name FROM mytable", "Data Source=localhost;database=test" );
            MySqlConnection conn = da.SelectCommand.Connection;
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            da.InsertCommand = new MySqlCommand( "INSERT INTO mytable (id, name) " + "VALUES (?id, ?name)", conn );
            da.UpdateCommand = new MySqlCommand( "UPDATE mytable SET id=?id, name=?name " + "WHERE id=?oldId", conn );
            da.DeleteCommand = new MySqlCommand( "DELETE FROM mytable WHERE id=?id", conn );
            da.InsertCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.InsertCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );

            da.UpdateCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            da.UpdateCommand.Parameters.Add( "?name", MySqlDbType.VarChar, 40, "name" );
            da.UpdateCommand.Parameters.Add( "?oldId", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
            da.DeleteCommand.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" ).SourceVersion = DataRowVersion.Original;
        }
        public static MySqlDataAdapter CreateCustomerAdapter( MySqlConnection conn )
        {
            MySqlDataAdapter da = new MySqlDataAdapter();
            MySqlCommand cmd;
            MySqlParameter parm;
            // Create the SelectCommand.
            cmd = new MySqlCommand( "SELECT * FROM mytable WHERE id=?id AND name=?name", conn );
            cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15 );
            cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15 );
            da.SelectCommand = cmd;
            // Create the DeleteCommand.
            cmd = new MySqlCommand( "DELETE FROM mytable WHERE id=?id", conn );
            parm = cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 5, "id" );
            parm.SourceVersion = DataRowVersion.Original;
            da.DeleteCommand = cmd;
            return da;
        }
        public static MySqlDataAdapter CreateCustomerAdapter2( MySqlConnection conn )
        {
            MySqlDataAdapter da = new MySqlDataAdapter();
            MySqlCommand cmd;
            //MySqlParameter parm;
            // Create the SelectCommand.
            cmd = new MySqlCommand( "SELECT * FROM mytable WHERE id=?id AND name=?name", conn );
            cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15 );
            cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15 );
            da.SelectCommand = cmd;
            // Create the InsertCommand.
            cmd = new MySqlCommand( "INSERT INTO mytable (id,name) VALUES (?id,?name)", conn );
            cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" );
            cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" );

            da.InsertCommand = cmd;
            return da;
        }
        public static MySqlDataAdapter CreateCustomerAdapter3( MySqlConnection conn )
        {
            MySqlDataAdapter da = new MySqlDataAdapter();
            MySqlCommand cmd;
            MySqlParameter parm;
            // Create the SelectCommand.
            cmd = new MySqlCommand( "SELECT * FROM mytable WHERE id=?id AND name=?name", conn );
            cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15 );
            cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15 );
            da.SelectCommand = cmd;
            // Create the UpdateCommand.
            cmd = new MySqlCommand( "UPDATE mytable SET id=?id, name=?name WHERE id=?oldId", conn );
            cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" );
            cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" );

            parm = cmd.Parameters.Add( "?oldId", MySqlDbType.VarChar, 15, "id" );
            parm.SourceVersion = DataRowVersion.Original;

            da.UpdateCommand = cmd;
            return da;
        }
        public static MySqlDataAdapter CreateCustomerAdapter4( MySqlConnection conn )
        {
            MySqlDataAdapter da = new MySqlDataAdapter();
            MySqlCommand cmd;
            //MySqlParameter parm;
            // Create the SelectCommand.
            cmd = new MySqlCommand( "SELECT * FROM mytable WHERE id=?id AND name=?name", conn );
            cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15 );
            cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15 );
            da.SelectCommand = cmd;
            // Create the InsertCommand.
            cmd = new MySqlCommand( "INSERT INTO mytable (id,name) VALUES (?id,?name)", conn );
            cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" );
            cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" );

            da.InsertCommand = cmd;
            return da;
        }
        public void ReadMyData( String myConnString )
        {
            String mySelectQuery = "SELECT OrderID, CustomerID FROM Orders";
            MySqlConnection myConnection = new MySqlConnection( myConnString );
            MySqlCommand myCommand = new MySqlCommand( mySelectQuery, myConnection );
            myConnection.Open();
            MySqlDataReader myReader;
            myReader = myCommand.ExecuteReader();
            // Always call Read before accessing data.
            while ( myReader.Read() )
            {
                Console.WriteLine( myReader.GetInt32( 0 ) + ", " + myReader.GetString( 1 ) );
            }
            // always call Close when done reading.
            myReader.Close();
            // Close the connection when done with it.
            myConnection.Close();
        }
        public void AddSqlParameters()
        {
            // ...
            // create myDataSet and myDataAdapter
            // ...
            //MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
            //DataSet myDataSet = new DataSet();
            //myDataAdapter.SelectCommand.Parameters.Add("@CategoryName", MySqlDbType.VarChar, 80).Value = "toasters";
            //myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", MySqlDbType.Long).Value = 239;
            //myDataAdapter.Fill(myDataSet);
        }

    }
}
